﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;
using IDAL;
namespace AccessDAL {
    public partial class Contact : IContact{
        public Contact() { }
        /// <summary>
        /// 根据分组ID，获取该分类下的联系人数量
        /// </summary>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public int GetContactCountByGroupId(int groupId) {
            StringBuilder sb = new StringBuilder();
            sb.Append("select count(*) from Contact");
            sb.Append(" where groupid = @groupid");
            OleDbParameter[] parameters =
            {
                new OleDbParameter("@groupid",SqlDbType.Int)
            };
            parameters[0].Value = groupId;
            int n = (int)OleDbHelper.ExecuteScalar(sb.ToString(), parameters);
            return n;
        }
        /// <summary>
        /// 根据查询条件，获取联系人列表
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataTable GetList(string strWhere) {
            StringBuilder sb = new StringBuilder();
            sb.Append("select contact.id,name,phone,email,qq,groupname from contact left join contactgroup on contact.groupid = contactgroup.id");
            if (strWhere.Trim() != "") {
                sb.Append(" where " + strWhere);
            }
            sb.Append(" order by contact.id desc");
            return OleDbHelper.ExecuteDataTable(sb.ToString());
        }
        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Add(Model.Contact model) {
            StringBuilder sb = new StringBuilder();
            sb.Append("insert into contact");
            sb.Append("(name,phone,email,qq,workunit,officephone,homeaddress,homephone,[memo],groupid)");
            sb.Append(" values (@name,@phone,@email,@qq,@officeaddress,@officephone,@homeaddress,@homephone,@memo,@groupid)");
            OleDbParameter[] parameters =
            {
                new OleDbParameter("@name",model.Name),
                new OleDbParameter("@phone",model.Phone),
                new OleDbParameter("@email",model.Email),
                new OleDbParameter("@qq",model.QQ),
                new OleDbParameter("@officeaddress",model.OfficeAddress),
                new OleDbParameter("@officephone",model.OfficePhone),
                new OleDbParameter("@homeaddress",model.HomeAddress),
                new OleDbParameter("@homephone",model.HomePhone),
                new OleDbParameter("@memo",model.Memo),
                new OleDbParameter("@groupid",model.GroupId)
            };
            int n = OleDbHelper.ExecuteNonQuery(sb.ToString(), parameters);
            if (n == 1) {
                return true;
            }
            else {
                return false;
            }
        }
        /// <summary>
        /// 删除指定ID的联系人
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public bool Delete(int Id) {
            StringBuilder sb = new StringBuilder();
            sb.Append("delete from contact where id =@id");
            OleDbParameter[] parameters = {
                new OleDbParameter("@id",SqlDbType.Int)
            };
            parameters[0].Value = Id;
            int n = OleDbHelper.ExecuteNonQuery(sb.ToString(), parameters);
            if (n == 1)
                return true;
            else
                return false;
        }
        /// <summary>
        /// 更新联系人信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Update(Model.Contact model) {
            StringBuilder sb = new StringBuilder();
            sb.Append("update contact set ");
            sb.Append("name = @name,phone = @phone,email = @email,qq= @qq,workunit = @officeaddress,");
            sb.Append("officephone = @officephone,homeaddress = @homeaddress,homephone = @homephone,");
            sb.Append("[memo] = @memo,groupid = @groupid");
            sb.Append(" where id = @id");
            OleDbParameter[] parameters = {
                new OleDbParameter("@name",model.Name),
                new OleDbParameter("@phone",model.Phone),
                new OleDbParameter("@email",model.Email),
                new OleDbParameter("@qq",model.QQ),
                new OleDbParameter("@officeaddress",model.OfficeAddress),
                new OleDbParameter("@officephone",model.OfficePhone),
                new OleDbParameter("@homeaddress",model.HomeAddress),
                new OleDbParameter("@homephone",model.HomePhone),
                new OleDbParameter("@memo",model.Memo),
                new OleDbParameter("@groupid",model.GroupId),
                new OleDbParameter("@id",model.Id)
            };
            int n = OleDbHelper.ExecuteNonQuery(sb.ToString(), parameters);
            if (n == 1)
                return true;
            else
                return false;
        }
        /// <summary>
        /// 根据联系人编号，获取联系人信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public Model.Contact GetModel(int Id) {
            StringBuilder sb = new StringBuilder();
            sb.Append("select * from contact where id = @id");
            OleDbParameter[] parameters = {
                new OleDbParameter("@id",SqlDbType.Int)
            };
            parameters[0].Value = Id;
            Model.Contact model = new Model.Contact();
            DataTable dt = OleDbHelper.ExecuteDataTable(sb.ToString(), parameters);
            if (dt.Rows.Count > 0) {
                model.Id = Convert.ToInt32(dt.Rows[0]["id"].ToString());
                model.Name = dt.Rows[0]["name"].ToString();
                model.Email = dt.Rows[0]["email"].ToString();
                model.Phone = dt.Rows[0]["phone"].ToString();
                model.QQ = dt.Rows[0]["qq"].ToString();
                model.OfficeAddress = dt.Rows[0]["workunit"].ToString();
                model.OfficePhone = dt.Rows[0]["officephone"].ToString();
                model.HomeAddress = dt.Rows[0]["homeaddress"].ToString();
                model.HomePhone = dt.Rows[0]["homephone"].ToString();
                model.Memo = dt.Rows[0]["memo"].ToString();
                model.GroupId = int.Parse(dt.Rows[0]["groupid"].ToString());
                return model;
            }
            else {
                return null;
            }
        }
    }
}
